package ru.fadeev.crammer.data;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

import ru.fadeev.crammer.DatabaseManager;
import ru.fadeev.crammer.TestManager;


/**
 * WordRecord class represents one of the main concept of Crammer program.
 * It contains word text, transcription, translation, some text 
 * using this word in some context and history of all tests.
 * @author Konstantin Fadeyev
 *
 */
public class WordRecord {
    //DB related fields    
    private long createDate;
    private Vector<TestHistoryRecord> historyRecords;
    private int id;
    private String prompt;	
 	private String transcription;
 	private String translation;
    private String word;
  
    
    //non-DB related fields
    
    /**
     * This field is similar to testWordsCount fields in Sheet and Dictionary 
     * classes, but it's called diffirent and has boolean type, because for 
     * WordRecord testWordsCount can have only two values: "Needed To Test" 
     * and "Not Needed To Test" 
     */
    private boolean[] neededToTest; 

	/**
	 * This field is similar to uncheckedWordsCount fields in Sheet and 
	 * Dictionary classes, but it's called diffirent and has boolean type, 
	 * because for WordRecord uncheckedWordsCount can have only two values: 
	 * "checked" and "unchecked" 
	 */
	private boolean[] unchecked;
	
	private WordsDataListener wordsDataListener;
    

	
	/**
	 * Creates WordRecord with default initial values
	 */
    public WordRecord() {
    	id = -1;
    	historyRecords = null;
    	neededToTest = new boolean[TestManager.TESTS_NUMBER];
    	unchecked = new boolean[TestManager.TESTS_NUMBER];
		for(int i = 0; i < TestManager.TESTS_NUMBER; i++) {
			neededToTest[i] = false;	
			unchecked[i] = true;
		}

		wordsDataListener = null;
    }
 
    
    /**
     * Adds history record to test history of WordRecord and saves it to database. 
     * Used by testers to save result of test.
     * @param testType one of TestManager test types
     * @param successful true if test for this WordRecord completed successfully
     */
    public void addHistoryRecord(int testType, boolean successful) {
    	if(historyRecords == null) {
    		try {
    			Connection connection = DatabaseManager.getConnection(); 
    			loadHistory(connection);
    			connection.close();
    		} catch(Exception e) {
    			e.printStackTrace();
    		}
    	}
    	TestHistoryRecord record = 
    		new TestHistoryRecord(System.currentTimeMillis(), testType, 
    				successful);
    	historyRecords.addElement(record);
    	
		try {
			SimpleDateFormat dateFormat = 
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
	    	Connection connection = DatabaseManager.getConnection();  
	    	Statement statement = 
	    		connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
			          ResultSet.CONCUR_UPDATABLE);
	    	CallableStatement identityStatement = 
	    		connection.prepareCall("{?= CALL IDENTITY()}");
	    	
			statement.executeUpdate("INSERT INTO TEST_HISTORY " +
						"(WORD_ID, SUCCESSFUL, " +
							"TEST_TYPE, " +
							"CREATE_DATE) " +
						"VALUES (" + id + ", " + record.isSuccessful() + "," + 
							record.getTestType() +",'" +
							dateFormat.format(new Date(record.getDate())) + "')");
			ResultSet resultSet = identityStatement.executeQuery();	
			resultSet.next();
			record.setId(resultSet.getInt(1));	
			
			identityStatement.close();
			statement.close();
			connection.close();
		} catch(Exception e) {
			e.printStackTrace();
		}  	
    }

    
    /**
     * Add record to test history without saving it to database
     * Used while importing words data from file.
     * @param record object of TestHistoryRecord to add to history 
     */
	void addHistoryRecord(TestHistoryRecord record) {
		if(historyRecords == null) {
			historyRecords = new Vector<TestHistoryRecord>();
		}
    	historyRecords.addElement(record);
    }

	
	/**
	 * Copies fields from WordRecord 'w' into this WordRecord.
	 * does not copy child Vector fields (like 'history')
	 * @param wordRecord object of WordRecord we copy content of
	 */
    public void copy(WordRecord wordRecord) {
    	id = wordRecord.id;
    	word = wordRecord.word;
    	transcription = wordRecord.transcription;
    	translation = wordRecord.translation;
    	prompt = wordRecord.prompt;
    	createDate = wordRecord.createDate;   
    	System.arraycopy(wordRecord.neededToTest, 0, neededToTest, 
    			0, TestManager.TESTS_NUMBER);
    	System.arraycopy(wordRecord.unchecked, 0, unchecked, 
    			0, TestManager.TESTS_NUMBER);
    }

    
    /**
     * Returns create date of WordRecord
     * @return create date of WordRecord
     */
	public long getCreateDate() {
    	return createDate;
    }
	
	
	/**
	 * Returns test history record for this WordRecord
	 * @param index index of record in history array
	 * @return test history record for this WordRecord
	 */
	public TestHistoryRecord getHistoryRecord(int index) {
    	if(historyRecords == null) {
    		try {
    			Connection connection = DatabaseManager.getConnection(); 
    			loadHistory(connection);
    			connection.close();
    		} catch(Exception e) {
    			e.printStackTrace();
    		}
    	}
    	return historyRecords.elementAt(index);
    }
	
	
	/**
	 * Returns number of records in test history
	 * @return number of records in test history
	 */
    public int getHistorySize() {
    	if(historyRecords == null) {
    		try {
    			Connection connection = DatabaseManager.getConnection(); 
    			loadHistory(connection);
    			connection.close();
    		} catch(Exception e) {
    			e.printStackTrace();
    		}
    	}
    	return historyRecords.size();
    }

	
	/**
     * Returns database table id of WordRecord
     * @return database table id of WordRecord
     */
	public int getId() {
		return id;
	}

    
    /**
	 * Returns prompt (text that uses word in some context)
	 * @return prompt (text that uses word in some context)
	 */
	public String getPrompt() {
        return prompt;
    }

	
	/**
	 * Returns transcription of this WordRecord
	 * @return transcription of this WordRecord
	 */
    public String getTranscription() {
		return transcription;
	}
	
	
	/**
	 * Returns translation of this WordRecord
	 * @return translation of this WordRecord
	 */
	public String getTranslation() {
		return translation;
	}

	
	/**
	 * Returns word text
	 * @return word text
	 */
	public String getWord() {
		return word;
	}
    
    
    /**
     * Inserts history records from memory into database. Used while importing
     * words data from file.
     */
    void insertHistoryToDB() {
    	if(historyRecords == null) {
    		historyRecords = new Vector<TestHistoryRecord>();
    	}
		try {
			SimpleDateFormat dateFormat = 
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
	    	Connection connection = DatabaseManager.getConnection();  
	    	Statement statement = 
	    		connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
			          ResultSet.CONCUR_UPDATABLE);
	    	CallableStatement identityStatement = 
	    		connection.prepareCall("{?= CALL IDENTITY()}");
			for(int i = 0; i < historyRecords.size(); i++) {
				TestHistoryRecord historyRecord = getHistoryRecord(i);
				if(historyRecord.getId() < 0) {
					statement.executeUpdate("INSERT INTO TEST_HISTORY " +
							"(WORD_ID, SUCCESSFUL, " +
								"TEST_TYPE, " +
								"CREATE_DATE) " +
							"VALUES (" + id + ", " 
								+ historyRecord.isSuccessful() + "," + 
								historyRecord.getTestType() +",'" +
								dateFormat.format(new Date(
										historyRecord.getDate())) + "')");
					ResultSet resultSet = identityStatement.executeQuery();	
					resultSet.next();
					historyRecord.setId(resultSet.getInt(1));			
				} else {
					statement.executeUpdate("INSERT INTO TEST_HISTORY " +
							"(ID, " +
							"WORD_ID, SUCCESSFUL, " +
								"TEST_TYPE, " +
								"CREATE_DATE) " +
							"VALUES (" + historyRecord.getId() + ", " + 
								id + ", " + historyRecord.isSuccessful() + "," + 
								historyRecord.getTestType() +",'" +
								dateFormat.format(new Date(
										historyRecord.getDate())) + "')");
				}
			}					
			identityStatement.close();
			statement.close();
			connection.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
    } 
    
    
    /**
     * Returns whether this WordRecord was checked for test needs or not. 
     * When Crammer starts all word records are unchecked. 
     * Then process of determining words test needs (TestNeedsWatcher) starts.
     * @param testType one of TestManager test types.
     * @return true if WordRecord was checked by TestNeedsWatcher 
     */
    public boolean isChecked(int testType) {
		return !unchecked[testType];
	}
    
    
    /**
     * Returns true if WordRecord marked as 'needs to be tested'. 
     * 'Needs to be tested' status refreshed by refreshNeededToTest() function
     * @param testType one of TestManager test types.
     * @return true if WordRecord needs to be tested in test specified by testType
     */
    boolean isNeededToTest(int testType) {
		return neededToTest[testType];
	}
	
    
    /**
     * Loads history from database. Used only by internal functions. History is 
     * loaded only when some outer object asks for history elements.
     * @param connection opened connection
     */
	private void loadHistory(Connection connection) {
		historyRecords = new Vector<TestHistoryRecord>();
		try {
			Statement statement = connection.createStatement();;
			ResultSet resultSet = statement.executeQuery(
        			"SELECT ID, SUCCESSFUL, TEST_TYPE, CREATE_DATE " +
        			"FROM TEST_HISTORY " +
        			"WHERE WORD_ID = " + id
        			);	
        	while(resultSet.next()) {
        		int id = resultSet.getInt(1);
        		boolean successful = resultSet.getBoolean(2);
        		int testType = resultSet.getInt(3);
        		Timestamp createDate = resultSet.getTimestamp(4);
        		
        		TestHistoryRecord newHistoryRecord = new TestHistoryRecord(id, 
        				createDate.getTime(), testType, successful);
        		historyRecords.addElement(newHistoryRecord);
        	}
        	statement.close();
		} catch (Exception e) {
	        e.printStackTrace();
	    }		
    }
	
	
	/**
	 * Determines whether this WordRecord needs to be tested or not based on
	 * test history of the WordRecord. Sets 'checked' status for testType 
	 * to true.
	 * @param testType one of TestManager test types
	 * @param dictionaryIndex index of dictionary containing WordRecord
	 * @param sheetIndex index of sheet containing WordRecord
	 * @return true if WordRecord needs to be tested
	 */
	public synchronized boolean refreshNeededToTest(int testType, 
			int dictionaryIndex, int sheetIndex) {
		if(unchecked[testType]) {
			if(TestManager.isNeededToTest(this, testType)) {
				neededToTest[testType] = true;				
			} else {
				neededToTest[testType] = false;
			}
			unchecked[testType] = false;
			if(wordsDataListener != null) {
				wordsDataListener.testWordsCountChanged(
						neededToTest[testType] ? 1 : 0, testType, true, 
								dictionaryIndex, sheetIndex);
			}
		} else {
			boolean oldValue = neededToTest[testType];
			if(TestManager.isNeededToTest(this, testType)) {
				neededToTest[testType] = true;				
			} else {
				neededToTest[testType] = false;
			}		
			if (oldValue != neededToTest[testType]) {
				if(wordsDataListener != null) {
					int change = (neededToTest[testType] ? 1 : 0) - 
							(oldValue ? 1 : 0);
					wordsDataListener.testWordsCountChanged(
							change, testType, true, dictionaryIndex, sheetIndex);
				}
			}
		}
		return neededToTest[testType];
	}
	
	
	/**
	 * Sets create date for WordRecord
	 * @param createDate new create date
	 */
	protected void setCreateDate(long createDate) {
    	this.createDate = createDate;
    }
	

	/**
	 * Sets database table id for WordRecord
	 * @param value new database table id
	 */
	protected void setId(int value) {
		id = value;
	}
		
	
	/**
	 * Sets prompt (text that uses word in some context)
	 * @param pr new prompt
	 */
    public void setPrompt(String pr) {
        prompt = pr;
    }

	
	/**
     * Sets word transcription
     * @param t new word transcription
     */
    public void setTranscription(String t) {
		transcription = t;
	}
    
    
    /**
     * Sets word translation
     * @param r new word translation
     */
    public void setTranslation(String r) {
		translation = r;
	}
    
    /**
	 * Sets new word text
	 * @param e new word text
	 */
	public void setWord(String e) {
		word = e;
	}
 
   
    /**
     * Sets new WordsDataListener
     * @param listener new WordsDataListener
     */
    public void setWordsDataListener(WordsDataListener listener) {
		wordsDataListener = listener;
	}
}
